package model.handlers;

import database.Database;
import java.sql.SQLException;
import model.*;
import utility.Converter;

/**
 *
 * @author Sonny Sandberg
 */
public abstract class OrderHandler 
{
    public static Order getLatestOrder(Order order) throws ClassNotFoundException, SQLException
    {
        int userID = order.getUser().getId();
        int taxID = order.getProgramValues().getId();
        
        Database db = new Database();
        
        String[][] result = db.getFromDB("bs_order", "WHERE fk_user_id = "+userID+" AND fk_tax = "+taxID+"", 
                                            "ORDER BY id DESC", "LIMIT 1", "id");
        db.closeDB();
        if (result.length > 0)
        {
            order.setId(Integer.parseInt(result[0][0]));
        }
        return order;
    }
    
    /**
     * CREATE OR REPLACE VIEW getAllOrders AS
        (
                SELECT
                        id AS orderID,
                        order_date AS orderDate,
                        pickup_date AS pickupDate,
                        delivery_date AS deliveryDate,
                        invoice_date AS invoiceDate,
                        payment_received AS paymentReceived,
                        finished,
                        comment,
                        fk_user_id AS userID,
                        fk_tax AS taxID,
                        fk_tombstone_order_id AS tombstoneID
                FROM
                        bs_order
        );
     * @param where "WHERE something = something"
     * @param order "ORDER BY something ASC or DESC"
     * @param limit "LIMIT 10"
     * @return Order order
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    public static Order[] getAllOrders(String where, String order, String limit) throws ClassNotFoundException, SQLException
    {
        Database db = new Database();
        Order[] orders = null;
        
        String[][] result = db.getFromDB("getAllOrders", where, order, limit, "orderID", "orderDate", "pickupDate", "deliveryDate", 
                                            "invoiceDate", "paymentReceived", "finished", "comment", "userID", "taxID", "tombstoneID");
        db.closeDB();
        
        orders = new Order[result.length];
        for (int i = 0; i < result.length; i++)
        {
            User user = UserHandler.getUserByID(Integer.parseInt(result[i][8]));
            ProgramValue programValue = ProgramValueHandler.getSpecificProgramValueModelObject(Integer.parseInt(result[i][9]));
            TombStone tombStone = null;
            if (result[i][10] != null)
            {
                tombStone = TombStoneHandler.getTombStoneByID(Integer.parseInt(result[i][10]));
            }
            
            CustomerSpecified[] customers = getCustomerSpecifiedByOrderID(Integer.parseInt(result[i][0]));
            
            orders[i] = new Order(Integer.parseInt(result[i][0]), result[i][7], Converter.intToBoolean(Integer.parseInt(result[i][6])), 
                                    result[i][1], result[i][2], result[i][3], result[i][4], Double.parseDouble(result[i][5]), 
                                    user, programValue, tombStone, customers);
            
            OrderLine[] orderLines = getOrderLineByOrderID(Integer.parseInt(result[i][0]));
            for (int j = 0; j < orderLines.length; j++)
            {
                orderLines[j].setOrder(orders[i]);
                orders[i].addOrderLine(orderLines[j]);                
            }
        }
        
        return orders;
    }
    
    /**
     * /**
     * CREATE OR REPLACE VIEW getAllOrders AS
        (
                SELECT
                        id AS orderID,
                        order_date AS orderDate,
                        pickup_date AS pickupDate,
                        delivery_date AS deliveryDate,
                        invoice_date AS invoiceDate,
                        payment_received AS paymentReceived,
                        finished,
                        comment,
                        fk_user_id AS userID,
                        fk_tax AS taxID,
                        fk_tombstone_order_id AS tombstoneID
                FROM
                        bs_order
        );
     * @param id
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    public static Order getOrderByID(int id) throws ClassNotFoundException, SQLException
    {
        Database db = new Database();
        Order order = null;
        
        String[][] result = db.getFromDB("getAllOrders", "WHERE orderID = "+id, "", "", "orderID", "orderDate", "pickupDate", "deliveryDate", 
                                            "invoiceDate", "paymentReceived", "finished", "comment", "userID", "taxID", "tombstoneID");
        db.closeDB();
        
        for (int i = 0; i < result.length; i++)
        {
            User user = UserHandler.getUserByID(Integer.parseInt(result[i][8]));
            ProgramValue programValue = ProgramValueHandler.getSpecificProgramValueModelObject(Integer.parseInt(result[i][9]));
            TombStone tombStone = null;
            if (result[i][10] != null)
            {
                tombStone = TombStoneHandler.getTombStoneByID(Integer.parseInt(result[i][10]));
            }
            
            CustomerSpecified[] customers = getCustomerSpecifiedByOrderID(Integer.parseInt(result[i][0]));
            
            order = new Order(Integer.parseInt(result[i][0]), result[i][7], Converter.intToBoolean(Integer.parseInt(result[i][6])), 
                                    result[i][1], result[i][2], result[i][3], result[i][4], Double.parseDouble(result[i][5]), 
                                    user, programValue, tombStone, customers);
            
            OrderLine[] orderLines = getOrderLineByOrderID(Integer.parseInt(result[i][0]));
            for (int j = 0; j < orderLines.length; j++)
            {
                orderLines[j].setOrder(order);
                order.addOrderLine(orderLines[j]);                
            }
        }
        
        return order;
    }
    
    public static CustomerSpecified[] getCustomerSpecifiedByOrderID(int id) throws ClassNotFoundException, SQLException
    {
        CustomerSpecified[] customers = null;
        
        Database db = new Database();
        String[][] result = db.getFromDB("bs_order_customer", "WHERE fk_order_id = "+id, "ORDER BY order_or_payer ASC", "LIMIT 2", "fk_customer_id");
        db.closeDB();
        
        customers = new CustomerSpecified[result.length];
        for (int i = 0; i < result.length; i++)
        {
            customers[i] = CustomerHandler.getCustomerSpecifiedByCustomerNameID(Integer.parseInt(result[i][0]));            
        }
        
        return customers;
    }
    
    public static OrderLine[] getOrderLineByOrderID(int id) throws ClassNotFoundException, SQLException
    {
        OrderLine[] orderLines = null;
        Database db = new Database();
        String[][] result = db.getFromDB("bs_order_line", "WHERE fk_order_id = "+id, "ORDER BY id ASC", "", "id", "amount", "unit_price", "fk_product_id", "fk_order_id");
        db.closeDB();
        
        orderLines = new OrderLine[result.length];
        for (int i = 0; i < result.length; i++)
        {
            Product product = ProductHandler.getProductByID(Integer.parseInt(result[i][3]));
            orderLines[i] = new OrderLine(Integer.parseInt(result[i][0]), product, null, Integer.parseInt(result[i][1]), Double.parseDouble(result[i][2]));            
        }
        
        return orderLines;
    }
}